﻿using System;
using System.Collections.Generic;
using Hjt212Parser;
using HpSocketIotGateway.Db.Code1st;
using HpSocketIotGateway.Properties;
using log4net;

namespace HpSocketIotGateway.Db
{
    /// <summary>
    /// 建议数据库路由和备份
    /// </summary>
    public static class DbRouteAndBackup
    {
        private static readonly ILog Log = LogManager.GetLogger(typeof(DbRouteAndBackup));
        private static readonly Dictionary<TableType, string> CreateTableStringList = new Dictionary<TableType, string>()
        {
            { TableType.received_msg,
                " (`id` bigint(20) NOT NULL AUTO_INCREMENT,"+
                "`received_datetime` datetime NOT NULL,"+
                "`received_string` varchar(2048) NOT NULL,"+
                "`is_length_right` bit(1) NOT NULL,"+
                "`qn` varchar(17) DEFAULT NULL,"+
                "`cn` varchar(4) DEFAULT NULL,"+
                "`mn` varchar(27) NOT NULL,"+
                "`processed_flag` varchar(20) NOT NULL,"+
                "`processed_flag_2` varchar(20) NOT NULL,"+
                "`processed_flag_3` varchar(20) NOT NULL,"+
                "`datatime` datetime DEFAULT NULL,"+
                "PRIMARY KEY (`id`),KEY `i_mn` (`mn`),KEY `i_cn` (`cn`),KEY `i_time` (`received_datetime`)"+
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8"},
            { TableType.send_ctr_cmd ,  "  (`id` bigint(20) NOT NULL AUTO_INCREMENT," +
                "`send_time` datetime NOT NULL,"+
                "`send_string` varchar(2048) NOT NULL,"+
                "`is_sent` bit(1) NOT NULL,"+
                "`sent_time` datetime DEFAULT NULL,"+
                "`qn` varchar(20) NOT NULL,"+
                "`cn` varchar(4) DEFAULT NULL,"+
                "`mn` varchar(27) NOT NULL,"+
                "`rtn_9011` varchar(4) DEFAULT NULL,"+
                "`rtn_9012` varchar(4) DEFAULT NULL,"+
                "`rtn_9014` varchar(4) DEFAULT NULL,"+
                "PRIMARY KEY (`id`),KEY `i_mn` (`mn`),KEY `i_cn` (`cn`),KEY `i_time` (`send_time`)"+
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8"},
            { TableType.send_data   ,  "  (`id` bigint(20) NOT NULL AUTO_INCREMENT," +
                "`send_time` datetime NOT NULL,"+
                "`send_string` varchar(2048) NOT NULL,"+
                "`is_sent` bit(1) NOT NULL,"+
                "`sent_time` datetime DEFAULT NULL,"+
                "`qn` varchar(20) NOT NULL,"+
                "`cn` varchar(4) DEFAULT NULL,"+
                "`mn` varchar(27) NOT NULL,"+
                "`rtn_9011` varchar(4) DEFAULT NULL,"+
                "`rtn_9012` varchar(4) DEFAULT NULL,"+
                "`rtn_9014` varchar(4) DEFAULT NULL,"+
                "PRIMARY KEY (`id`),KEY `i_mn` (`mn`),KEY `i_cn` (`cn`),KEY `i_time` (`send_time`)"+
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8"}
        };

        private static string GenSuffix(DateTime dateTime, int mouthsPerTable)
        {
            if (mouthsPerTable > 12 || mouthsPerTable < 1) mouthsPerTable = 12;
            var num = (dateTime.Month-1) / mouthsPerTable + 1;
            return dateTime.ToString("yyyy") + "_" + 12/mouthsPerTable + "_" + num;
        }
        private static void CreateTable(TableType type, String suffix)
        {
            var tableName = Enum.GetName(typeof(TableType), type) + suffix;
            if (CreateTableStringList.TryGetValue(type, out var ddlRear))
            {
                ExecuteNonQuery("create table if not exists " + tableName + ddlRear);
            }
        }

        public static void InitTable()
        {
            if (CreateTableStringList.TryGetValue(TableType.received_msg, out var ddlRear))
            {
                ExecuteNonQuery("create table if not exists received_msg " + ddlRear);
            }
            if (CreateTableStringList.TryGetValue(TableType.send_ctr_cmd, out ddlRear))
            {
                ExecuteNonQuery("create table if not exists send_ctr_cmd " + ddlRear);
            }
            if (CreateTableStringList.TryGetValue(TableType.send_data, out ddlRear))
            {
                ExecuteNonQuery("create table if not exists send_data " + ddlRear);
            }
            var createControllerSql = @"create table if not exists controller (`id` bigint(20) NOT NULL AUTO_INCREMENT,`mn` varchar(50) NOT NULL,`feature` varchar(50) NOT NULL,PRIMARY KEY(`id`),UNIQUE INDEX `mn`(`mn`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8";
            ExecuteNonQuery(createControllerSql);
        }

        private static void ExecuteNonQuery(string sql)
        {
            try
            {
                ThreadUtil.Sleep(10);
                using (var ctx = new GatewayContext())
                {
                    ctx.Database.ExecuteSqlCommand(sql);
                }
            }
            catch (Exception ex)
            {
                Log.Error(ex+Environment.NewLine+sql);
                throw;
            }
        }


        public static void SynDataReceived(List<long> ids)
        {
            string idSet = string.Join(",", ids);
            var now = DateTime.Now;
            var monthsPerTable = Settings.Default.BackupMonthsTo1Table;
            if (monthsPerTable > 12 || monthsPerTable < 1) monthsPerTable = 12;
            var suffix = GenSuffix(now, monthsPerTable);
            var tableName = Enum.GetName(typeof(TableType), TableType.received_msg) +suffix;
            CreateTable(TableType.received_msg, suffix);
            string tmp = "INSERT INTO " + tableName + "(received_datetime,received_string,is_length_right,qn,cn,mn,processed_flag,processed_flag_2,processed_flag_3,datatime)"
                + " SELECT received_datetime,received_string,is_length_right,qn,cn,mn,processed_flag,processed_flag_2,processed_flag_3,datatime" + " FROM received_msg where id in(" + idSet + ")";
            ExecuteNonQuery(tmp);
            tmp = "DELETE FROM received_msg where id in(" + idSet + ")";
            ExecuteNonQuery(tmp);
        }

        public static void SynSendCtrCmd(List<long> ids)
        {
            string idSet = string.Join(",", ids);
            var now = DateTime.Now;
            var monthsPerTable = Settings.Default.BackupMonthsTo1Table;
            if (monthsPerTable > 12 || monthsPerTable < 1) monthsPerTable = 12;
            var suffix = GenSuffix(now, monthsPerTable);
           // var suffix = GenSuffix(now, 12);
            var tableName = Enum.GetName(typeof(TableType), TableType.send_ctr_cmd) + suffix;
            CreateTable(TableType.send_ctr_cmd, suffix);
            string tmp = "INSERT INTO " + tableName + "(send_time,send_string,is_sent,sent_time,qn,cn,mn,rtn_9011,rtn_9012,rtn_9014)"
                + " SELECT send_time,send_string,is_sent,sent_time,qn,cn,mn,rtn_9011,rtn_9012,rtn_9014" + " FROM send_ctr_cmd where id in(" + idSet + ")";
            ExecuteNonQuery(tmp);
            tmp = "DELETE FROM send_ctr_cmd where id in(" + idSet + ")";
            ExecuteNonQuery(tmp);
        }

        public static void SynSendData(List<long> ids)
        {
            string idSet = string.Join(",", ids);
            var now = DateTime.Now;
            var monthsPerTable = Settings.Default.BackupMonthsTo1Table;
            if (monthsPerTable > 12 || monthsPerTable < 1) monthsPerTable = 12;
            var suffix = GenSuffix(now, monthsPerTable);
            // var suffix = GenSuffix(now, 12);
            var tableName = Enum.GetName(typeof(TableType), TableType.send_data) + suffix;
            CreateTable(TableType.send_data, suffix);
            string tmp = "INSERT INTO " + tableName + "(send_time,send_string,is_sent,sent_time,qn,cn,mn,rtn_9011,rtn_9012,rtn_9014)"
                + " SELECT send_time,send_string,is_sent,sent_time,qn,cn,mn,rtn_9011,rtn_9012,rtn_9014" + " FROM send_data where id in(" + idSet + ")";
            ExecuteNonQuery(tmp);
            tmp = "DELETE FROM send_data where id in(" + idSet + ")";
            ExecuteNonQuery(tmp);
        }
    }

}
